package org.feluma.sgci.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.feluma.sgci.entidade.Curso;
import org.feluma.sgci.util.Conexao;
import org.feluma.sgci.util.Excecao;

public class CursoDAO {

	public CursoDAO() throws Excecao{}
	
	public ArrayList<Curso> listar(int codEscola) throws Excecao{
		Connection conexao = Conexao.getConexaoMssql();
		PreparedStatement ps = null;
    	ResultSet rs = null;
    	ArrayList<Curso> lista = new ArrayList<Curso>();
    	String sql = " select distinct codEsc, codCur, nomCur,qtde = (select COUNT(distinct cpfCan) from cadastro where codEsc = cur.codesc and codCur = cur.codcur)" +
    			     " from vwcurso cur where codEsc = ? order by qtde desc, nomCur";
    	try {
    		ps = conexao.prepareStatement(sql);
    		ps.setInt(1, codEscola);
    		rs = ps.executeQuery();
    		
    		while(rs.next()){
    			lista.add(new Curso(rs.getInt("codEsc"),rs.getInt("codCur"),rs.getString("nomCur")));
    		} 	
		} catch (Exception e) {
			return lista;
		} finally {
			Conexao.closeConnection(conexao, ps, rs);
		}
		return lista;		
	}
	
	public ArrayList<Curso> listar(int codEscola, int codProcesso) throws Excecao{
		Connection conexao = Conexao.getConexaoMssql();
		PreparedStatement ps = null;
    	ResultSet rs = null;
    	ArrayList<Curso> lista = new ArrayList<Curso>();
    	String sql = " select distinct codEsc, codCur, nomCur " +
    			     " from vwcurso cur where codEsc = ? and codvest = ? order by  nomCur";
    	try {
    		ps = conexao.prepareStatement(sql);
    		ps.setInt(1, codEscola);
    		ps.setInt(2, codProcesso);
    		rs = ps.executeQuery();
    		
    		while(rs.next()){
    			lista.add(new Curso(rs.getInt("codEsc"),rs.getInt("codCur"),rs.getString("nomCur")));
    		} 	
		} catch (Exception e) {
			return lista;
		} finally {
			Conexao.closeConnection(conexao, ps, rs);
		}
		return lista;		
	}
	
	public Curso pesquisar(int codEscola,int codCurso) throws Excecao{
		Connection conexao = Conexao.getConexaoMssql();
		PreparedStatement ps = null;
    	ResultSet rs = null;
    	Curso curso = new Curso();
    	String sql = " select distinct codEsc, codCur, nomCur from vwcurso where codesc = ? and codCur = ? ";
    	
    	try {
    		ps = conexao.prepareStatement(sql);
    		ps.setInt(1, codEscola);
    		ps.setInt(2, codCurso);
    		rs = ps.executeQuery();    		
    		while(rs.next()){
    			curso = new Curso(rs.getInt("codEsc"),rs.getInt("codCur"),rs.getString("nomCur"));
    		} 	
		} catch (Exception e) {
			return curso;
		} finally {
			Conexao.closeConnection(conexao, ps, rs);
		}
		return curso;
	}
	
	public Curso procurar(int codCurso) throws Excecao{
		Connection conexao = Conexao.getConexaoMssql();
		PreparedStatement ps = null;
    	ResultSet rs = null;
    	Curso curso = new Curso();
    	String sql = " select codEsc, codCur, nomCur from vwcurso where codCur = ?  order by nomCur";
    	
    	try {
    		ps = conexao.prepareStatement(sql);
    		ps.setInt(1, codCurso);
    		rs = ps.executeQuery();
    		
    		while(rs.next()){
    			curso = new Curso(rs.getInt("codEsc"),rs.getInt("codCur"),rs.getString("nomCur"));
    		} 	
		} catch (Exception e) {
			return curso;
		} finally {
			Conexao.closeConnection(conexao, ps, rs);
		}
		return curso;		
	}
	
	public ArrayList<String> listarProcesso() throws Excecao{
		ArrayList<String> lista = new ArrayList<String>();
		Connection conexao = Conexao.getConexaoMssql();
		PreparedStatement ps = null;
    	ResultSet rs = null;
    	String sql = "select distinct  titulo = convert(varchar(10),codvest) + '&' + nome, nome  from vwcurso order by nome ";
		
    	try {
    		ps = conexao.prepareStatement(sql);
    		rs = ps.executeQuery();
    		
    		while(rs.next()){
    			lista.add(rs.getString("titulo"));
    		} 	
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Conexao.closeConnection(conexao, ps, rs);
		}	
		return lista;
	}
	
	public String statusCurso(Curso objCurso) throws Excecao{
		
		Connection conexao = Conexao.getConexaoMssql();
		PreparedStatement ps = null;
    	ResultSet rs = null;
    	String status = "Fechado";
    	String sql = " select distinct codEsc, codCur from vwcurso " +
    			     " where codEsc = ? and getdate() between datIni and datFim " +
    			     "       and codcur = ? order by nomCur";    	
    	try {
    		ps = conexao.prepareStatement(sql);
    		ps.setInt(1, objCurso.getCodigoEscola());
    		ps.setInt(2, objCurso.getCodigoCurso());
    		rs = ps.executeQuery();    		
    		while(rs.next()){
    			status = "Aberto";
    		} 	
		} catch (Exception e) {
			status = "Fechado";
		} finally {
			Conexao.closeConnection(conexao, ps, rs);
		}		
		return status;
	}
	
	public Integer qtdeCandidatosCurso(Curso objCurso) throws Excecao{
		Connection conexao = Conexao.getConexaoMssql();
		PreparedStatement ps = null;
    	ResultSet rs = null;
    	int quantidade = 0;
    	String sql = "select qtde = COUNT(distinct nomCan)  from cadastro where codEsc = ? and codCur = ?";    	
    	try {
    		ps = conexao.prepareStatement(sql);
    		ps.setInt(1, objCurso.getCodigoEscola());
    		ps.setInt(2, objCurso.getCodigoCurso());
    		
    		rs = ps.executeQuery();    		
    		if(rs.next()){
    			quantidade = rs.getInt("qtde");
    		} 	
		} catch (Exception e) {
			quantidade = 0;
		} finally {
			Conexao.closeConnection(conexao, ps, rs);
		}		
		return quantidade;
	}
	
	public String getProcesso(Curso objCurso) throws Excecao{
		Connection conexao = Conexao.getConexaoMssql();
		PreparedStatement ps = null;
    	ResultSet rs = null;
    	String processo ="" ;
    	String sql = " select distinct nome from vwcurso where codEsc = ? and codcur = ?  ";    	
    	try {
    		ps = conexao.prepareStatement(sql);
    		ps.setInt(1, objCurso.getCodigoEscola());
    		ps.setInt(2, objCurso.getCodigoCurso());
    		rs = ps.executeQuery();    		
    		while(rs.next()){
    			processo = rs.getString("nome");
    		} 
    	} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Conexao.closeConnection(conexao, ps, rs);
		}
		return processo;
	}

	public ArrayList<Curso> pesquisar(String nome, int codEscola) throws Excecao {
		Connection conexao = Conexao.getConexaoMssql();
		PreparedStatement ps = null;
    	ResultSet rs = null;
    	ArrayList<Curso> listaCurso = new ArrayList<Curso>();
    	String sql = " select distinct codEsc, codCur, nomCur,qtde = (select COUNT(distinct cpfCan) from cadastro where codEsc = cur.codesc and codCur = cur.codcur)" +
    			     " from vwcurso cur where codEsc = ? and nomCur like '%"+nome+"%' order by qtde desc, nomCur";    	
    	try {
    		ps = conexao.prepareStatement(sql);
    		ps.setInt(1, codEscola);
    		rs = ps.executeQuery();    		
    		while(rs.next()){
    			listaCurso.add( new Curso(rs.getInt("codEsc"),rs.getInt("codCur"),rs.getString("nomCur")));
    		} 	
		} catch (Exception e) {
			return listaCurso;
		} finally {
			Conexao.closeConnection(conexao, ps, rs);
		}
		return listaCurso;
	}
	
	public ArrayList<Curso> pesquisar(String nome, String processo, int codEscola) throws Excecao {
		Connection conexao = Conexao.getConexaoMssql();
		PreparedStatement ps = null;
    	ResultSet rs = null;
    	ArrayList<Curso> listaCurso = new ArrayList<Curso>();
    	String sql = " select  distinct codEsc, codCur, nomCur, processo = nome,qtde = (select COUNT(distinct cpfCan) from cadastro where codEsc = cur.codesc and codCur = cur.codcur)" +
    			     " from vwcurso cur where codEsc = ? and nomCur like '%"+nome+"%' and nome  like '%" + processo + "%' order by qtde desc, nomCur";    	
    	try {
    		ps = conexao.prepareStatement(sql);
    		ps.setInt(1, codEscola);
    		rs = ps.executeQuery();    		
    		while(rs.next()){
    			listaCurso.add( new Curso(rs.getInt("codEsc"),rs.getInt("codCur"),rs.getString("nomCur")));
    		} 	
		} catch (Exception e) {
			return listaCurso;
		} finally {
			Conexao.closeConnection(conexao, ps, rs);
		}
		return listaCurso;
	}
}
